insert overwrite table jms_dm.dm_network_order_delivery_mid_hi
select
     pick_network_code as network_code
    ,pick_network_name as network_name
    ,count(1) as order_cnt
    ,sum(if_center_arrival) as jj_cnt
    ,sum(if_center_arrival) / count(1) as jj_rate
    ,to_date(input_time) as dt
from (
    select
         der.id                as order_no
        ,der.waybill_id        as waybill_no
        ,der.input_time        as input_time
        ,dim.code              as pick_network_code
        ,dim.name              as pick_network_name
        ,case when arr.waybill_no is not null then 1 else 0 end as if_center_arrival
    from (
        select
           id,waybill_id,input_time,pick_network_code
        from jms_dwd.dwd_yl_oms_oms_order_hf
        where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',3) and date_add('{{ execution_date | cst_ds }}',4)
        and to_date(input_time) = date_sub('{{ execution_date | cst_ds }}',1)
        and order_status_code <> 104
    ) der
    left join (
        select *
            from (
            select
                 waybill_no
                ,scan_site_code
                ,scan_time
                ,row_number() over(partition by waybill_no order by scan_time asc) as rn 
            from jms_dwd.dwd_tab_barscan_centerarrival_base_hi
            where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
              and scan_time between concat(date_sub('{{ execution_date | cst_ds }}',1),' 00:00:00') and '{{ execution_date | cst_ds }} 07:59:59'
        ) a where a.rn = 1
    ) arr on der.waybill_id = arr.waybill_no
    left join jms_dim.dim_network_whole_massage dim 
       on dim.code = der.pick_network_code --当前
) a group by pick_network_code
            ,pick_network_name
            ,to_date(input_time)
distribute by dt,pmod(hash(rand()),1);

insert overwrite table jms_dm.dm_network_order_delivery_summary_hi
select
     network_code    
    ,max(network_name) as network_name
    ,sum(order_cnt) as order_cnt      
    ,sum(jj_cnt   ) as jj_cnt         
    ,sum(jj_cnt) / sum(order_cnt) as jj_rate         
    ,date_sub('{{ execution_date | cst_ds }}',1) as dt
from jms_dm.dm_network_order_delivery_mid_hi
where dt between date_sub('{{ execution_date | cst_ds }}',3) and date_sub('{{ execution_date | cst_ds }}',1)
group by network_code
distribute by dt,pmod(hash(rand()),1);